package com.mc.dao.Impl;

import com.mc.VO.ScoreVO;
import com.mc.dao.ScoreVODao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description 参与团队成绩视图数据访问接口实现类
 * @author wangcf
 * @date 2023/2/15 11:51
 * @version 1.0
 */
public class ScoreVODaoImpl extends BaseDao implements ScoreVODao {

    public ScoreVO newScoreVO() throws SQLException {
        ScoreVO scoreVO=new ScoreVO(rs.getInt(1),rs.getString(2),rs.getInt(3),
                rs.getString(4),rs.getInt(5),rs.getInt(6),
                rs.getString(7),rs.getInt(8));
        return scoreVO;
    }

    public ScoreVO newScoreVO2() throws SQLException {
        ScoreVO scoreVO=new ScoreVO(rs.getInt(3),
                rs.getString(4),rs.getInt(5),rs.getInt(6),
                rs.getString(7),rs.getInt(8),rs.getInt(1),rs.getString(2));
        return scoreVO;
    }

    @Override
    public List<ScoreVO> findByTid(int tid) {
        List<ScoreVO> scoreVOList=new ArrayList<>();
        try {
            regist();
            connection();
            getPst("select teacher.`tid`,teacher.`tname`,tt.`teamid`,team.`teamname`,apply.`matchcode`,apply.`mid`,`match`.`mname`,SUM(score.`score`)" +
                    "from tt,teacher,apply,score,team,`match` where tt.`tid`=teacher.`tid` and tt.`teamid`=apply.`teamid` and apply.`matchcode`=score.`matchcode` and tt.`teamid`=team.`teamid` and `match`.`mid`=apply.`mid` and teacher.`tid`=? group by apply.`matchcode`;");
            pstmt.setInt(1,tid);
            getRs();
            while (rs.next()){
                ScoreVO scoreVO=newScoreVO();
                scoreVOList.add(scoreVO);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return scoreVOList;
    }

    @Override
    public List<ScoreVO> findBySid(int sid) {
        List<ScoreVO> scoreVOList=new ArrayList<>();
        try {
            regist();
            connection();
            getPst("select student.`sid`,student.`sname`,st.`teamid`,team.`teamname`,apply.`matchcode`,apply.`mid`,`match`.`mname`,SUM(score.`score`)" +
                    "from st,student,apply,score,team,`match` where st.`sid`=student.`sid` and st.`teamid`=apply.`teamid` and apply.`matchcode`=score.`matchcode` and st.`teamid`=team.`teamid` and `match`.`mid`=apply.`mid` and student.`sid`=? group by apply.`matchcode`;");
            pstmt.setInt(1,sid);
            getRs();
            while (rs.next()){
                ScoreVO scoreVO=newScoreVO2();
                scoreVOList.add(scoreVO);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return scoreVOList;
    }
}
